package com.yonyou.brigade.order_center.util;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.lang.reflect.Field;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 功能: [POI实现把Excel数据导入到数据库]
 * 作者: sz
 * 版本: 1.0
 */
public class ImportExcel {

    //正则表达式 用于匹配属性的第一个字母
    private final String REGEX = "[a-zA-Z]";

    /**
     * 功能: Excel数据导入到数据库
     * 参数: originUrl[Excel表的所在路径]
     * 参数: startRow[从第几行开始]
     * 参数: endRow[到第几行结束
     *                  (0表示所有行;
     *                  正数表示到第几行结束;
     *                  负数表示到倒数第几行结束)]
     * 参数: clazz[要返回的对象集合的类型]
     */
    public  List<?> importExcel(MultipartFile originInputStream, int startRow, int endRow, Class<?> clazz) throws IOException {
        //是否打印提示信息
        boolean showInfo=true;
        return doImportExcel(originInputStream,startRow,endRow,showInfo,clazz);
    }

    /**
     * 功能: Excel数据导入到数据库
     * 参数: originUrl[Excel表的所在路径]
     * 参数: startRow[从第几行开始]
     * 参数: endRow[到第几行结束
     *                  (0表示所有行;
     *                  正数表示到第几行结束;
     *                  负数表示到倒数第几行结束)]
     */
    public  List<Map<String,Object>> importExcel(MultipartFile originInputStream, int startRow, int endRow) throws IOException {
        //是否打印提示信息
        boolean showInfo=true;
        return doImportExcel(originInputStream,startRow,endRow,showInfo);
    }

    /**
     * 功能:真正实现导入,如果传表头表头需要制定字段 例：年份{year};如果不传表头则按字段顺序赋值
     * startRow：1:从标题行开始;2:从数据开始
     * endRow：结束行0为所有
     */
    private  List<Map<String,Object>> doImportExcel(MultipartFile originInputStream, int startRow, int endRow, boolean showInfo) throws IOException {
        List<Row> rowList = new ArrayList<>();
        Boolean isHaveHead = readExcelRowInfo(originInputStream,startRow,endRow,showInfo,rowList);
        return returnObjectList(rowList,isHaveHead);
    }

    /**
     * 功能:返回指定的对象集合
     */
    public  List<Map<String,Object>> returnObjectList(List<Row> rowList, Boolean isHaveHead) throws IOException {
        List<Map<String,Object>> objectList=null;
        try {
            objectList=new ArrayList<Map<String,Object>>();
            //字段名
            String[] attrs = null;
            if(isHaveHead){
                /**表头行**/
                Row headRow = rowList.get(0);
                attrs = new String[headRow.getLastCellNum()];
                for (int i = 0; i < headRow.getLastCellNum(); i++) {
                    String headValue  = getCellValue(headRow.getCell(i));
                    if(headValue.indexOf("{") > 0){
                        attrs[i] = headValue.substring(headValue.indexOf("{")+1,headValue.indexOf("}"));
                    }else{
                        attrs[i] = i+"";
                    }
                }
            }

            // 获取每一单元格的值
            for (int k = 0; k < rowList.size(); k++) {
                Map<String,Object> attrToValueMap = new HashMap<>();
                // 获取每一单元格的值
                for (int i = 0; i < rowList.get(k).getLastCellNum(); i++) {
                    attrToValueMap.put(attrs[i], getCellValue(rowList.get(k).getCell(i)));
                }
                objectList.add(attrToValueMap);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return objectList;
    }

    /**
     *
     * @param originInputStream 输入流
     * @param startRow  1:从标题行开始;2:从数据开始
     * @param endRow 结束行0为所有
     * @param showInfo 是否打印提示信息
     * @param rowList 解析的excel 行信息 （不能传null  会将excel解析的行放入rowList中）
     * @throws IOException
     * @ return  返回是否有表头信息
     *
     */
    private Boolean  readExcelRowInfo(MultipartFile originInputStream, int startRow, int endRow, boolean showInfo, List<Row> rowList)throws IOException {
        // 判断文件是否存在
        if (originInputStream.getSize() <= 0) {
            throw new IOException("文件不存在！");
        }
        // 去读Excel
        Workbook wb = null;
        try {
            wb = new HSSFWorkbook(originInputStream.getInputStream());
        } catch (Exception ex){
            // 兼容2007 2010版本
            wb = new XSSFWorkbook(originInputStream.getInputStream());
        }
        Sheet sheet = wb.getSheetAt(0);
        // 获取最后行号
        int lastRowNum = sheet.getLastRowNum();
        int fristRowNum = sheet.getFirstRowNum();
        if (lastRowNum > 0) { // 如果>0，表示有数据
            out("\n开始读取名为【" + sheet.getSheetName() + "】的内容：",showInfo);
        }
        Row row = null;
        // 循环读取
        for (int i = startRow; i <= lastRowNum + endRow; i++) {
            row = sheet.getRow(i);
            if (row != null) {
                rowList.add(row);
                out("第" + (i + 1) + "行：",showInfo,false);
                // 获取每一单元格的值
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    String value = getCellValue(row.getCell(j));
                    if (!value.equals("")) {
                        out(value + " | ",showInfo,false);
                    }
                }
                out("",showInfo);
            }
        }
        Boolean isHaveHead = true;
        if(startRow > 0){
            isHaveHead = false;
        }
        return  isHaveHead;
    }
    /**
     * 获取excel行数据
     * @param originInputStream
     * @param sheetIndex
     * @param startRow
     * @param endRow
     * @param showInfo
     * @return
     * @throws IOException
     */
    public List<Row> getRowDatas(POIFSFileSystem originInputStream, int sheetIndex, int startRow, int endRow, boolean showInfo)throws IOException {
        // 判断文件是否存在
        if (originInputStream.getBigBlockSize() <= 0) {
            throw new IOException("文件不存在！");
        }
        // 去读Excel
        HSSFWorkbook wb = new HSSFWorkbook(originInputStream);
        List<Row> rowList = new ArrayList<Row>();
        Sheet sheet = wb.getSheetAt(sheetIndex);
        // 获取最后行号
        int lastRowNum = sheet.getLastRowNum();
        if (lastRowNum > 0) { // 如果>0，表示有数据
            out("\n开始读取名为【" + sheet.getSheetName() + "】的内容",showInfo);
        }
        Row row = null;
        // 循环读取
        for (int i = startRow; i <= lastRowNum + endRow; i++) {
            row = sheet.getRow(i);
            if (row != null) {
                rowList.add(row);
            }
        }
        return rowList;
    }

    /**
     * 功能:真正实现导入,如果传表头表头需要制定字段 例：年份{year};如果不传表头则按字段顺序赋值
     * startRow：1:从标题行开始;2:从数据开始
     * endRow：结束行0为所有
     */
    private  List<Object> doImportExcel(MultipartFile originInputStream, int startRow, int endRow, boolean showInfo, Class<?> clazz) throws IOException {
        List<Row> rowList = new ArrayList<>();
        Boolean isHaveHead = readExcelRowInfo(originInputStream,startRow,endRow,showInfo,rowList);
        return returnObjectList(rowList,clazz,isHaveHead);
    }

    /**
     * 功能:获取单元格的值
     */
    public String getCellValue(Cell cell) {
        Object result = "";
        if (cell != null) {
            //解决日期03-五月-2018格式读入后的问题，POI读取后变成“02-十一月-2018”格式
            if (cell.toString().contains("-")) {
                if (checkDate(cell.toString())) {
                    return new SimpleDateFormat("yyyy/MM/dd").format(cell.getDateCellValue());
                } else if (checkStringDate(cell.toString())){
                    return cell.getStringCellValue().replaceAll("-", "/");
                }

            }

            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    result = cell.getStringCellValue();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    result = this.subZeroAndDot(String.valueOf(cell.getNumericCellValue()));
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    result = cell.getBooleanCellValue();
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    result = ((XSSFCell) cell).getRawValue();
                    break;
                case Cell.CELL_TYPE_ERROR:
                    result = cell.getErrorCellValue();
                    break;
                case Cell.CELL_TYPE_BLANK:
                    break;
                default:
                    break;
            }
        }
        return result.toString();
    }

    private boolean checkStringDate(String str) {
        String[] dataArr = str.split("-");
        boolean result = true;
        if(dataArr.length == 3){
            SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd");
            try {
                Date date = format.parse(str);
            } catch (ParseException e) {
                result = false;
            }
        } else {
            result = false;
        }
        return result;
    }

    /**
     * 判断是否是“02-十一月-2018”格式的日期类型
     */
    private boolean checkDate(String str){
        String[] dataArr =str.split("-");
        try {
            if(dataArr.length == 3){
                int x = Integer.parseInt(dataArr[0]);
                String y =  dataArr[1];
                int z = Integer.parseInt(dataArr[2]);
                if(x>0 && x<32 && z>0 && z< 10000 && y.endsWith("月")){
                    return true;
                }
            }
        } catch (Exception e) {
            return false;
        }
        return false;
    }

    /**
     * 使用java正则表达式去掉多余的.与0
     * @param s
     * @return
     */
    public String subZeroAndDot(String s){
        if(s.indexOf(".") > 0){
            s = s.replaceAll("0+?$", "");//去掉多余的0
            s = s.replaceAll("[.]$", "");//如最后一位是.则去掉
        }
        return s;
    }

    /**
     * 功能:返回指定的对象集合
     */
    public  List<Object> returnObjectList(List<Row> rowList, Class<?> clazz, Boolean isHaveHead) throws IOException {
        List<Object> objectList=null;
        Object obj=null;
        String attribute=null;
        String value=null;
        int j=0;
        try {
            objectList=new ArrayList<Object>();
            Field[] declaredFields = clazz.getDeclaredFields();
            //字段名
            String[] attrs = null;
            if(isHaveHead){
                //表头行
                Row headRow = rowList.get(0);
                attrs = new String[headRow.getLastCellNum()];
                for (int i = 0; i < headRow.getLastCellNum(); i++) {
                    String headValue  = getCellValue(headRow.getCell(i));
                    if(headValue.indexOf("{") > 0){
                        attrs[i] = headValue.substring(headValue.indexOf("{")+1,headValue.indexOf("}"));
                    }else{
                        attrs[i] = headValue;
                    }
                }
            }
            //字段与值对应关系
            Map<String,String> attrToValueMap = new HashMap<String,String>();
            // 获取每一单元格的值
            for (int k = 0; k < rowList.size(); k++) {
                if(isHaveHead) {
                    attrToValueMap.clear();
                    // 获取每一单元格的值
                    for (int i = 0; i < rowList.get(k).getLastCellNum(); i++) {
                        attrToValueMap.put(attrs[i], getCellValue(rowList.get(k).getCell(i)));
                    }
                }
                obj = clazz.newInstance();
                if(isHaveHead) {
                    for (String fieldAttr : attrs) {
                        VOReflectTool.setAttrributeValue(obj,fieldAttr,attrToValueMap.get(fieldAttr));
                    }
                }else{
                    j=0;
                    for (Field field : declaredFields) {
                        attribute=field.getName().toString();
                        value = getCellValue(rowList.get(k).getCell(j));
                        VOReflectTool.setAttrributeValue(obj,attribute,value);
                        j++;
                    }
                }
                objectList.add(obj);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return objectList;
    }

    /**
     * 功能:输出提示信息(普通信息打印)
     */
    public  void out(String info, boolean showInfo) {
        if (showInfo) {
            System.out.print(info + (showInfo ? "\n" : ""));
        }
    }

    /**
     * 功能:输出提示信息(同一行的不同单元格信息打印)
     */
    public  void out(String info, boolean showInfo, boolean nextLine) {
        if (showInfo) {
            if(nextLine)
            {
                System.out.print(info + (showInfo ? "\n" : ""));
            }else
            {
                System.out.print( info );
            }
        }
    }
}
